﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;

using BLL.Services.Users.Listing;

namespace BLL.Services.Users
{
    public class UsersCollection 
    {
        private readonly string _connectionString;

        public UsersCollection(string connectionString)
        {
            _connectionString = connectionString;
        }

        /// <summary>
        /// Selects the by id.
        /// </summary>
        /// <param name="userId">The user id.</param>
        public User SelectById(int userId)
        {
            User result = null;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Users.SelectById(conn, userId);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                        result = GetUser(reader);
                }
            }
            return result;
        }

        /// <summary>
        /// Selects all.
        /// </summary>
        public IEnumerable<User> SelectAll()
        {
            var result = new List<User>();
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Users.SelectAll(conn);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var user = GetUser(reader);
                        result.Add(user);
                    }
                }
            }
           return result;
        }

        /// <summary>
        /// Creates the specified model.
        /// </summary>
        /// <param name="model">The model.</param>
        public void Create(User model)
        {
            model.RegistrationDate = DateTime.Now;
            model.LastUpdateDate = DateTime.Now;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Users.Insert(
                    conn, model.Avatar, model.Email, model.PasswordHash, model.Salt, model.Login, model.RegistrationDate, (byte)model.Status, model.LastUpdateDate);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (reader.HasRows)
                        {
                            var user = GetUser(reader);
                            model.UserId = user.UserId;

                        }
                    }
                }
            }
        }

        public User GetByEmailOrLogin(string email)
        {
            User result = null;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Users.GetByEmailOrLogin(conn, email);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                        result = GetUser(reader);
                }
            }
            return result;
        }
        /// <summary>
        /// Saves the specified model.
        /// </summary>
        /// <param name="model">The model.</param>
        public void Save(User model)
        {
            model.LastUpdateDate = DateTime.Now;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Users.Update(
                    conn, model.UserId, model.Avatar, model.Email, model.PasswordHash, model.Salt, model.Login, model.RegistrationDate, (byte)model.Status, model.LastUpdateDate);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        model = GetUser(reader);
                    }
                }
            }
        }

        public void ProfileUpdate(User model)
        {
            model.LastUpdateDate = DateTime.Now;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Users.ProfileUpdate(
                    conn, model.UserId, model.Avatar, model.Email, model.PasswordHash, model.Salt, model.Login, model.RegistrationDate, (byte)model.Status, model.LastUpdateDate);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        model = GetUser(reader);
                    }
                }
            }
        }

        public bool ValidateUser(int userId, string email, string login)
        {
            var result = false;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Users.Validate(conn, userId, login, email);

                var isValidParameter = new SqlParameter("@valid", SqlDbType.Bit, 4)
                    { Direction = ParameterDirection.ReturnValue };
                cmd.Parameters.Add(isValidParameter);

                cmd.ExecuteNonQuery();
                result = (int)isValidParameter.Value == 1;
            }
            return result;
        }

        /// <summary>
        /// Deletes the specified user id.
        /// </summary>
        /// <param name="userId">The user id.</param>
        public void Delete(int userId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Users.Delete(conn, userId);

                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Gets the user listing.
        /// </summary>
        /// <param name="page">The page.</param>
        /// <param name="size">The size.</param>
        /// <param name="column">The column.</param>
        /// <param name="direction">The direction.</param>
        /// <param name="filter">The filter.</param>
        public UserListing GetUserListing(int page, int size, string column, int direction, UserFilter filter)
        {
            if (filter == null)
                filter = new UserFilter();

            var result = new UserListing
            {
                Page = page,
                Size = size,
                Direction = direction,
                Column = column,
                Filter = filter
            };

            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Users.GetListing(
                    conn, page, size, column, direction, filter.Email, filter.Login);

                var totalCount = new SqlParameter("@TotalCount", SqlDbType.Int, 4)
                {
                    Direction = ParameterDirection.ReturnValue
                };
                cmd.Parameters.Add(totalCount);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var item = new UserListing.User
                            {
                                UserId = reader.GetInt32(0),
                                Email = reader[1] as string,
                                Login = reader[2] as string,
                                Date = reader.GetDateTime(3)
                            };
                        result.Users.Add(item);
                    }
                }
                result.TotalPages = (int)totalCount.Value % size == 0
                                        ? (int)totalCount.Value / size
                                        : ((int)totalCount.Value / size) + 1;
            }
            return result;
        }

        /// <summary>
        /// Get user from data reader
        /// </summary>
        /// <param name="reader">The reader.</param>
        private static User GetUser(SqlDataReader reader)
        {
            var user = new User
                {
                    UserId = reader.GetInt32(0),
                    Avatar = reader[1] as string,
                    Email = reader[2] as string,
                    PasswordHash = reader.GetSqlBytes(3).Value,
                    Salt = reader.GetSqlBytes(4).Value,
                    Login = reader[5] as string,
                    RegistrationDate = reader.GetDateTime(6),
                    Status = (UserStatus)reader.GetByte(7),
                    LastUpdateDate = reader.GetDateTime(8)
                };
            return user;
        }
    }
}